package com.cms.biz.business.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cms.biz.business.CMSAccreditationContractServiceMgr;
import com.cms.persistence.contractManager.accreditationContract.PSHTMX;
import com.common.util.DateUtil;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.dctbuilder.rule.EMPCodeRuler;
import com.efounder.dict.DMTServiceMgr;
import com.efounder.dict.FCTServiceMgr;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.form.server.resolver.util.SYS_MDL_VAL;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.framework.sys.business.AbstractBusinessObjectServiceMgr;
import com.metadata.biz.MetaDataServiceMgr;
import com.metadata.bizmodel.SYS_MODEL;
import com.pub.util.StringFunction;

import dwz.dal.common.sequence.UUIDGenerator;

@Transactional(rollbackFor = Exception.class)
@Service("CMSAccreditationContractMgr")
public class CMSAccreditationContractMgrImpl extends AbstractBusinessObjectServiceMgr implements CMSAccreditationContractServiceMgr {
	
	@Autowired
	private JdbcTemplate                    jdbcTemplate;
	
	@Autowired
	private MetaDataServiceMgr         metaDataServiceMgr;

	@Autowired
	private DMTServiceMgr                      DMTService;
	
	@Autowired
	private FCTServiceMgr                      FCTService;
	
	public EFDataSet getHTZDMXDataSetRow(String F_FLBH, String F_HTBH) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance("HTZDMX");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			strSql = "select HTZDMX.F_FLBH as F_FLBH, HTZDMX.F_HTBH as F_HTBH, HTZDMX.F_CPBH as F_CPBH,"
				   + " HTCPZD.F_CPMC as F_CPMC, HTCPZD.F_GGXH as F_GGXH, round(HTZDMX.F_DJ, 2) AS F_DJ, round(HTZDMX.F_SL, 2) AS F_SL, round(HTZDMX.F_DJ*HTZDMX.F_SL, 2) AS F_JE"
				   + " from HTZDMX"
				   + " LEFT JOIN HTCPZD ON HTZDMX.F_CPBH = HTCPZD.F_CPBH"
				   + " where 1 = 1 ";
			if(F_HTBH != null && F_HTBH.trim().length() > 0) {
				strSql += " and HTZDMX.F_HTBH = '" + F_HTBH + "'";
			}
			if(F_FLBH != null && F_FLBH.trim().length() > 0) {
				strSql += " and HTZDMX.F_FLBH = '" + F_FLBH + "'";
			}
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dataset;
	}

	public JResponseObject updatePSHTFormHead(JParamObject PO, EFRowSet rowset) {
		JConnection         conn = null;
		JStatement          stmt = null;
		ResultSet             rs = null;
		JResponseObject       RO = new JResponseObject();
		String            F_DJBH = rowset.getString("F_DJBH", "");
		String            strSql = "";
		long            F_CHDATE = DateUtil.currentTimestamp().getTime();
		long                  l1 = 0;
		long                  l2 = 0;
		String           F_CDATE = "";
		EFDataSet         formDS = null;
		EFRowSet          fromRS = null;
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();
			if(F_DJBH == null || F_DJBH.trim().length() == 0) {
				rowset = GetNewBillCode(conn, PO, rowset);
				rowset.putString("F_CHDATE", "" + F_CHDATE);
				rowset.putString("F_CRDATE", "" + F_CHDATE);
				RO = FCTService.insertFCTRow(PO.GetValueByParamName("TABLENAME", ""), rowset);
			} else {
				strSql = "select " + PO.GetValueByParamName("TABLENAME", "") + ".F_ZDR, USER_NAME as F_ZDRMC, " + PO.GetValueByParamName("TABLENAME", "") + ".F_CHDATE from " + PO.GetValueByParamName("TABLENAME", "") 
					   + " left join BSUSER on BSUSER.USER_ID = " + PO.GetValueByParamName("TABLENAME", "") + ".F_ZDR"
				       + " where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
				rs = stmt.executeQuery(strSql);
				formDS = DataSetUtils.resultSet2DataSet(rs, formDS);
				fromRS = formDS.getRowSet(0);
				F_CDATE = fromRS.getString("F_CHDATE", "");
				l1 = Long.parseLong(F_CDATE);
				l2 = Long.parseLong(rowset.getString("F_CHDATE", ""));
				if(l1 > l2) {
					Date dd = new Date(l2);
					RO.setErrorCode(-1);
					RO.setErrorString("单据已由【" + fromRS.getString("F_ZDRMC", "") + "】在【" + DateUtil.date2String(dd, DateUtil.PATTERN_STANDARD) + ")】进行了修改，请重新打开单据后维护保存！");
					return RO;
				}
				rowset.putString("F_CHDATE", "" + F_CHDATE);
				RO = DMTService.updateRow(PO.GetValueByParamName("TABLENAME", ""), rowset);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return RO;
	}
	
	public EFRowSet GetNewBillCode(JConnection conn, JParamObject PO, EFRowSet rowset) throws Exception {
		String           F_DJBH = rowset.getString("F_DJBH", "");
		String           MDL_ID = PO.GetValueByParamName(SYS_MODEL.MODEL_ID, "");
		EFDataSet      mdlValDS = metaDataServiceMgr.getSYS_MDL_VAL(MDL_ID);
		EFRowSet       mdlValRS;
		String            newBH = "";
		String           BMSTRU = "";
		EMPCodeRuler  codeRuler = new EMPCodeRuler();
		JParamObject      param = JParamObject.Create();
		int               bmLen = 0;
		
		if(F_DJBH == null || F_DJBH.trim().length() == 0) {
			mdlValRS = (EFRowSet) mdlValDS.getRowSet(SYS_MDL_VAL.BILL_DJBH_BHFS);
			param.SetValueByParamName("CODERULE", mdlValRS.getString(SYS_MDL_VAL.MDL_VALUE, ""));
			
			mdlValRS = (EFRowSet) mdlValDS.getRowSet(SYS_MDL_VAL.BILL_DJBH_LEN);
			BMSTRU = mdlValRS.getString(SYS_MDL_VAL.MDL_VALUE, "");
			bmLen = StringFunction.getStrutLength(BMSTRU);
			
			param.SetValueByParamName("DCT_BMSTRU_LENGTH", "" + bmLen);
			param.SetValueByParamName(SYS_MODEL.MODEL_ID, MDL_ID);
			param.SetValueByParamName("F_DATE", rowset.getString("F_KJND", ""));
			
			newBH = codeRuler.getNextModelBHFromDB(conn, param, null);
			rowset.putString("F_DJBH", newBH);
		}
		return rowset;
	}

	
	public JResponseObject updatePSHTFormItem(JParamObject PO, EFRowSet rowset) {
		JConnection         conn = null;
		JStatement          stmt = null;
		ResultSet             rs = null;
		JResponseObject       RO = new JResponseObject();
		String            strSql = "";
		long         currentTime = DateUtil.currentTimestamp().getTime();
		long                  l1 = 0;
		long                  l2 = 0;
		String           F_CDATE = "";
		EFDataSet         formDS = null;
		EFRowSet          fromRS = null;
		EFDataSet    itemDataset = EFDataSet.getInstance(new String[]{"F_BMBH"});
		EFDataSet     newDataset = rowset.getDataSet(PSHTMX.class.getName());
		EFRowSet       newRowset = null;
		String          F_FLGUID = UUIDGenerator.nextId();
		long           F_FLINDEX = DateUtil.currentTimestamp().getTime();
		List<String>     sqlList = new ArrayList<String>();
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			
			//判断修改日期
			strSql = "select PSHTXX.F_KJND, PSHTXX.F_ZDR, PSHTXX.F_DJBH, USER_NAME as F_ZDRMC, PSHTXX.F_CHDATE from PSHTXX" 
				   + " left join BSUSER on BSUSER.USER_ID = PSHTXX.F_ZDR"
			       + " where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
			rs = stmt.executeQuery(strSql);
			formDS = DataSetUtils.resultSet2DataSet(rs, formDS);
			fromRS = formDS.getRowSet(0);
			F_CDATE = fromRS.getString("F_CHDATE", "");
			l1 = Long.parseLong(F_CDATE);
			l2 = Long.parseLong(rowset.getString("F_CHDATE", ""));
			if(l1 > l2) {
				Date dd = new Date(l2);
				RO.setErrorCode(-1);
				RO.setErrorString("单据已由【" + fromRS.getString("F_ZDRMC", "") + "】在【" + DateUtil.date2String(dd, DateUtil.PATTERN_STANDARD) + ")】进行了修改，请重新打开单据后维护保存！");
				return RO;
			}
			
			//更新单据头的修改日期
			strSql = "update PSHTXX set F_CHDATE = '" + currentTime + "' where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
			sqlList.add(strSql);
			//查询现有的分录数据
			strSql = "select F_BMBH from PSHTMX where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
			rs = stmt.executeQuery(strSql);
			itemDataset = DataSetUtils.resultSet2DataSet(rs, itemDataset);
			for(int i = 0; i < newDataset.getRowCount(); i++) {
				newRowset = newDataset.getRowSet(i);
				if(itemDataset.getRowSet(newRowset.getString("F_BMBH", "")) == null) {
					strSql = "insert into PSHTMX (F_GUID, F_DJBH, F_KJND, F_FLBH, F_BMBH, F_CRDATE, F_CHDATE) values('" 
						   + rowset.getString("F_GUID", "") + "','" + fromRS.getString("F_DJBH", "") + "','" + fromRS.getString("F_KJND", "") + "','"
						   + F_FLINDEX + F_FLGUID + "','" + newRowset.getString("F_BMBH", "") + "','" + currentTime + "','" + currentTime + "')";
					sqlList.add(strSql);
					F_FLINDEX++;
				}
			}
			for(int i = 0; i < sqlList.size(); i++) {
				stmt.execute(sqlList.get(i));
			}
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			RO.setErrorCode(-1);
			RO.setErrorString(e.getCause().getMessage());
			e.printStackTrace();
			return RO;
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		RO.setErrorCode(0);
		RO.setResponseObject(currentTime);
		return RO;
	}

	public JResponseObject checkPSHTForm(JParamObject PO, String F_CHDATE) {
		JConnection         conn = null;
		JStatement          stmt = null;
		ResultSet             rs = null;
		JResponseObject       RO = new JResponseObject();
		String            strSql = "";
		long                  l1 = 0;
		long                  l2 = 0;
		String           F_CDATE = "";
		EFDataSet         formDS = null;
		EFRowSet          fromRS = null;
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();
			strSql = "select " + PO.GetValueByParamName("TABLENAME", "") + ".F_ZDR, USER_NAME as F_ZDRMC, " + PO.GetValueByParamName("TABLENAME", "") + ".F_CHDATE from " + PO.GetValueByParamName("TABLENAME", "") 
			       + " left join BSUSER on BSUSER.USER_ID = " + PO.GetValueByParamName("TABLENAME", "") + ".F_ZDR"
			       + " where F_GUID = '" + PO.GetValueByParamName("F_GUID", "") + "'";
			rs = stmt.executeQuery(strSql);
			formDS = DataSetUtils.resultSet2DataSet(rs, formDS);
			fromRS = formDS.getRowSet(0);
			F_CDATE = fromRS.getString("F_CHDATE", "");
			l1 = Long.parseLong(F_CDATE);
			l2 = Long.parseLong(F_CHDATE);
			if(l1 > l2) {
				Date dd = new Date(l2);
				RO.setErrorCode(-1);
				RO.setErrorString("单据已由【" + fromRS.getString("F_ZDRMC", "") + "】在【" + DateUtil.date2String(dd, DateUtil.PATTERN_STANDARD) + ")】进行了修改，请重新打开单据后维护保存！");
				return RO;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		RO.setErrorCode(0);
		return RO;
	}

	@Override
	public JResponseObject deletePSHTFormItem(JParamObject PO, EFRowSet rowset) {
		JConnection         conn = null;
		JStatement          stmt = null;
		ResultSet             rs = null;
		JResponseObject       RO = new JResponseObject();
		String            strSql = "";
		long         currentTime = DateUtil.currentTimestamp().getTime();
		long                  l1 = 0;
		long                  l2 = 0;
		String           F_CDATE = "";
		EFDataSet         formDS = null;
		EFRowSet          fromRS = null;
		EFDataSet    itemDataset = EFDataSet.getInstance(new String[]{"F_BMBH"});
		EFDataSet     newDataset = rowset.getDataSet(PSHTMX.class.getName());
		EFRowSet       newRowset = null;
		List<String>     sqlList = new ArrayList<String>();
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			
			//判断修改日期
			strSql = " select PSHTXX.F_KJND, PSHTXX.F_ZDR, PSHTXX.F_DJBH, USER_NAME as F_ZDRMC, PSHTXX.F_CHDATE from PSHTXX" 
				   + " left join BSUSER on BSUSER.USER_ID = PSHTXX.F_ZDR"
			       + " where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
			rs = stmt.executeQuery(strSql);
			formDS = DataSetUtils.resultSet2DataSet(rs, formDS);
			fromRS = formDS.getRowSet(0);
			F_CDATE = fromRS.getString("F_CHDATE", "");
			l1 = Long.parseLong(F_CDATE);
			l2 = Long.parseLong(rowset.getString("F_CHDATE", ""));
			if(l1 > l2) {
				Date dd = new Date(l2);
				RO.setErrorCode(-1);
				RO.setErrorString("单据已由【" + fromRS.getString("F_ZDRMC", "") + "】在【" + DateUtil.date2String(dd, DateUtil.PATTERN_STANDARD) + ")】进行了修改，请重新打开单据后维护保存！");
				return RO;
			}
			
			//更新单据头的修改日期
			strSql = "update PSHTXX set F_CHDATE = '" + currentTime + "' where F_GUID = '" + rowset.getString("F_GUID", "") + "'";
			sqlList.add(strSql);
			
			for(int i = 0; i < newDataset.getRowCount(); i++) {
				newRowset = newDataset.getRowSet(i);
				if(itemDataset.getRowSet(newRowset.getString("F_BMBH", "")) == null) {
					strSql = "delete from PSHTMX where F_GUID = '" + rowset.getString("F_GUID", "") + "' and F_FLBH = '" + newRowset.getString("F_FLBH", "") + "'";
					sqlList.add(strSql);
				}
			}
			for(int i = 0; i < sqlList.size(); i++) {
				stmt.execute(sqlList.get(i));
			}
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			RO.setErrorCode(-1);
			RO.setErrorString(e.getCause().getMessage());
			e.printStackTrace();
			return RO;
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		RO.setErrorCode(0);
		RO.setResponseObject(currentTime);
		return RO;
	}

}
